﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text.RegularExpressions;
using System.IO;
using System.Windows.Forms;
using SpectationClient;
using SpectationClient.Stuff;
using SpectationClient.SQLCommandBuilder;
//using SharpMap.Geometries;
using SpectationClient.DataBaseDescription;
//using ToolLibrary.BackgroundWorker;
//using ToolLibrary.Forms;
using SpectationClient.SpectralTools;
using Npgsql;

namespace SpectationClient.GUI {
    public partial class SubInsertMessprotokollGFZ {
        private void BGW_INSERT_DoWork(object sender, DoWorkEventArgs e) {
            BackgroundWorker meself = (BackgroundWorker)sender;
            BGWInfo_INS_GFZ bgi = (BGWInfo_INS_GFZ)e.Argument;
            String lastCommand = "";
            bgi.con.Open();
            NpgsqlTransaction trans = bgi.con.BeginTransaction(IsolationLevel.Serializable);

            
            bgi.cmdSPEC.Connection = bgi.con;
            bgi.cmdSPEC.Transaction = trans;

            bool done = false;


            try {

                if(meself.CancellationPending) return;
                //Füge Grunddaten ein
                meself.ReportProgress(5);
                meself.ReportProgress(5, "Add Basic-Informations");
                //meself.ReportProgress(1, "Add to CORE.LOCATIONS...");
                //DB_ID id_soil = null;
                if(bgi.cmdSoil != null) {
                    meself.ReportProgress(6, "Add to C_GFZ.SOILS...");
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdSoil);
                    bgi.id_soil = (Int32?)CommandHelper.getReturningScalar(bgi.cmdSoil, bgi.con, trans);
                   
                }

                meself.ReportProgress(10);
                if(meself.CancellationPending) return;
                Int32? id_location = null;
                if(bgi.cmdLocation != null) {
                    meself.ReportProgress(10, "Add to CORE.LOCATIONS...");
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdLocation);
                    id_location = (Int32?)CommandHelper.getReturningScalar(bgi.cmdLocation, bgi.con, trans);
                }

                Int32? id_weather = null;
                meself.ReportProgress(15);
                if(bgi.cmdWeather != null) {
                    meself.ReportProgress(15, "Add to C_GZF.WEATHER...");
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdWeather);
                    id_weather = (Int32?) CommandHelper.getReturningScalar(bgi.cmdWeather, bgi.con, trans);
                }

                if(meself.CancellationPending) return;
                meself.ReportProgress(20, "Add to CORE.SPECTRA...");
                lastCommand = CommandBuilder.CommandToString(bgi.cmdSPEC);
                DataTable idsSpectra = CommandHelper.getReturningTable(bgi.cmdSPEC, bgi.con, trans);
                Type specidtype = idsSpectra.Rows[0][0].GetType();

                if(meself.CancellationPending) return;
                meself.ReportProgress(30);
                DataTable ids_wref = null;
                if(bgi.cmdWREF != null) {
                    meself.ReportProgress(30, "Add to CORE.WREF_SPECTRA...");
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdWREF);
                    ids_wref = CommandHelper.getReturningTable(bgi.cmdWREF, bgi.con, trans);
                }

                DataTable idsPhotos_NtoM = null;
                DataTable idsPhotos_1toM = null;

                if(meself.CancellationPending) return;
                meself.ReportProgress(40);
                if(bgi.cmdPhotos_1toM != null || bgi.cmdPhotos_NtoM != null) {
                    meself.ReportProgress(40, "Add to CORE.PHOTOS...");

                    if(bgi.cmdPhotos_NtoM != null) {
                        lastCommand = CommandBuilder.CommandToString(bgi.cmdPhotos_NtoM);
                        idsPhotos_NtoM = CommandHelper.getReturningTable(bgi.cmdPhotos_NtoM, bgi.con, trans);
                    }
                    if(bgi.cmdPhotos_1toM != null) {
                        lastCommand = CommandBuilder.CommandToString(bgi.cmdPhotos_1toM);
                        idsPhotos_1toM = CommandHelper.getReturningTable(bgi.cmdPhotos_1toM, bgi.con, trans);
                    }
                    if(meself.CancellationPending) return;
                    //PHOTOS   
                    meself.ReportProgress(50, "Add to CORE.BT_PHOTOS...");
                    TableInfo tiBT_PHOTOS = bgi.DBINFO["CORE"]["BT_PHOTOS"];
                    InsertValueCollection rvcPhotos = new InsertValueCollection(ref tiBT_PHOTOS);
                   
                    //N:M Photos
                    if(idsPhotos_NtoM != null) {
                        foreach(DataRow r_s in idsSpectra.Rows) {
                            foreach(DataRow r_p in idsPhotos_NtoM.Rows) {
                                InsertValues rvPhotos = new InsertValues(ref tiBT_PHOTOS);
                                rvPhotos.Add("id_spectrum", r_s["id"]);
                                rvPhotos.Add("id_photo", r_p["id"]);
                                rvcPhotos.Add(rvPhotos);
                 
                            }
                        }
                    }
                    //1:N Photos
                    if(idsPhotos_1toM != null) {
                        DataHelper.print(idsPhotos_1toM);
                        DataHelper.print(bgi.LUT_BT_PHOTOS);
                        foreach(DataRow lut_row in bgi.LUT_BT_PHOTOS.Rows) {
                            InsertValues rvPhotos = new InsertValues(ref tiBT_PHOTOS);
                            rvPhotos.Add("id_spectrum", idsSpectra.Rows[(Int32)lut_row["id_spectrum"]]["id"]);
                            rvPhotos.Add("id_photo", idsPhotos_1toM.Rows[(Int32)lut_row["id_photo"]]["id"]);
                            rvcPhotos.Add(rvPhotos);
                        
                        }
                    }

                    if(rvcPhotos.Count > 0) {
                        NpgsqlCommand cmd = rvcPhotos.getInsertCmd(false, bgi.con, trans);
                        cmd.ExecuteNonQuery();
                    }
             

                }

                if(meself.CancellationPending) return;
                meself.ReportProgress(60);
                if(bgi.LUT_SPECTRA_PROCESSES != null && bgi.LUT_SPECTRA_PROCESSES.Rows.Count > 0) {
                    meself.ReportProgress(60, "Add processing informations ...");
                    TableInfo ti = bgi.DBINFO["CORE"]["PROCESSING"];
                    InsertValueCollection rvcProcessing = new InsertValueCollection(ref ti);
           
                    
                    int cR = bgi.LUT_SPECTRA_PROCESSES.Rows.Count;
                    foreach(DataRow R in bgi.LUT_SPECTRA_PROCESSES.Rows) {
                        InsertValues rvProcessing = new InsertValues(ref ti);
                        rvProcessing.Add("id_parent", idsSpectra.Rows[(Int32)R["id_parent"]]["id"]);
                        rvProcessing.Add("id_child", idsSpectra.Rows[(Int32)R["id_child"]]["id"]);
                        rvProcessing.Add("process", R["process"]);
                        rvcProcessing.Add(rvProcessing);
                 
                    }
                    NpgsqlCommand cmd_Processing = rvcProcessing.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmd_Processing);
                    cmd_Processing.ExecuteNonQuery();
                }

                if(meself.CancellationPending) return;

                meself.ReportProgress(63);
                //DB_ID id_veg_ps = null;
                if(bgi.cmdVEG_PS != null) {
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdVEG_PS);
                    bgi.id_veg_ps = (Int32?) CommandHelper.getReturningScalar(bgi.cmdVEG_PS, bgi.con, trans);
                }

                DataTable ids_veg_sp = null;
                if(bgi.cmdVEG_SP != null) {
                    lastCommand = CommandBuilder.CommandToString(bgi.cmdVEG_SP);
                    ids_veg_sp = CommandHelper.getReturningTable(bgi.cmdVEG_SP, bgi.con, trans);
                }

                if(meself.CancellationPending) return;
                //VEG - Plant Societies
                meself.ReportProgress(65);
                if(bgi.id_veg_ps != null) {
                    meself.ReportProgress(65, "Add plant-society...");
                    TableInfo tiBTPS = bgi.DBINFO["C_GFZ"]["BT_PLANT_SOCIETIES"];
                    InsertValueCollection rvcBTPS = CommandHelper.getBridgeTableRowValueCollection(
                        tiBTPS, idsSpectra, bgi.id_veg_ps, "id_spectrum", "id_plant_society");

                    NpgsqlCommand cmdBTPS = rvcBTPS.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmdBTPS);
                    cmdBTPS.ExecuteNonQuery();
                }

                if(meself.CancellationPending) return;
                //VEG - Single Plants
                meself.ReportProgress(70);
                if(ids_veg_sp != null) {
                    meself.ReportProgress(70, "Add to C_GFZ.BT_PLANTS...");
                    TableInfo tiBTSP = bgi.DBINFO["C_GFZ"]["BT_PLANTS"];
                    InsertValueCollection rvc = CommandHelper.getBridgeTableRowValueCollection(tiBTSP, idsSpectra, ids_veg_sp, "id_spectrum", "id_plant");

                    NpgsqlCommand cmd = rvc.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmd);
                    cmd.ExecuteNonQuery();
                }

                if(meself.CancellationPending) return;
                //SOILS
                meself.ReportProgress(75);
                if(bgi.id_soil != null) {
                    meself.ReportProgress(75, "Add to C_GFZ.BT_SOILS...");
                    TableInfo tiBTSOILS = bgi.DBINFO["C_GFZ"]["BT_SOILS"];
                    InsertValueCollection rvc = CommandHelper.getBridgeTableRowValueCollection(tiBTSOILS, idsSpectra, bgi.id_soil, "id_spectrum", "id_soil");
                    NpgsqlCommand cmd = rvc.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmd);
                    cmd.ExecuteNonQuery();
       
                }

                if(meself.CancellationPending) return;
                //WEATHER
                meself.ReportProgress(90);
                if(id_weather != null) {
                    meself.ReportProgress(90, "Add to C_GFZ.BT_WEATHER...");
                    TableInfo tiBTWeather = bgi.DBINFO["C_GFZ"]["BT_WEATHER"];
                    InsertValueCollection rvc = CommandHelper.getBridgeTableRowValueCollection(tiBTWeather, idsSpectra, id_weather, "id_spectrum", "id_weather");
                    NpgsqlCommand cmd = rvc.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmd);
                    cmd.ExecuteNonQuery();

                }

                if(meself.CancellationPending) return;
                 
                //WREF
                meself.ReportProgress(95);
                if(ids_wref != null) {
                    meself.ReportProgress(95, "Add to CORE.BT_WREF_SPECTRA");
                    TableInfo tiBTWeather = bgi.DBINFO["CORE"]["BT_WREF_SPECTRA"];
                    InsertValueCollection rvc = CommandHelper.getBridgeTableRowValueCollection(tiBTWeather, idsSpectra, ids_wref, "id_spectrum", "id_wref");
                    NpgsqlCommand cmd = rvc.getInsertCmd(false, bgi.con, trans);
                    lastCommand = CommandBuilder.CommandToString(cmd);
                    cmd.ExecuteNonQuery();

                }

#if DEBUG
                // throw new Exception("TEST");
                System.Threading.Thread.Sleep(1);
#endif
                
                
                if(meself.CancellationPending) return;
                meself.ReportProgress(100);
                
                
                done = true;

            } catch(Exception ex) {
                MessageBox.Show("Last Command:\n" + lastCommand);
                throw ex;
            } finally {
                //commit / rollback
                if(!done || meself.CancellationPending) {
                    trans.Rollback();
                } else {
                    #if DEBUG
                        Console.WriteLine("TEST - Rollback!");
                        trans.Rollback();
                    #else
                        trans.Commit();
                    #endif
                }
                //close connection
                bgi.con.Close();

                
                if(meself.CancellationPending) {
                    e.Cancel = true;
                }
            }
        }

        

        void BGW_INSERT_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
           
            if(e.Error != null) {
                TSL_Action.Text = "Fehler beim Einfügen";
                FormHelper.ShowErrorBox(e.Error);
            } else {
                if(e.Cancelled) {
                    MessageBox.Show("Einfügen abgebrochen", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);

                } else {
                    MessageBox.Show("Daten erfolgreich in die Datenbank eingefügt", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    FormHelper.ResetControls(this);
                    EL.checkforDisposedElements();
                    gb_POS_none_rb.Checked = true;
                }
                TSL_Action.Text = "";
                TSL_ProgressBar.Value = 0;
            }

            this.FindForm().Enabled = true;

        }
    }
}
